﻿using System;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using TeacherLeftHand.Model;
using log4net;
using TeacherLeftHand.Util;
using System.Data;
using System.Data.SqlClient;
using TeacherLeftHand.TLH_DB_DataSetTableAdapters;

namespace TeacherLeftHand.DataAccess
{
    /// <summary>
    /// Knowledge Item Accessor
    /// - LoadTeachingPlan, insert, update, delete data in file xml
    /// - binding source to the main view
    /// </summary>
    public class KnowledgeItemAccessor
    {
        #region Constants

        // XML document FUll File Path (../bin/debug/...xml)
        //String XmlFile = AppDomain.CurrentDomain.BaseDirectory + "_ki.xml";
        //public static string Filename = "C:\\_ki.xml";

        #endregion

        #region Variables

        protected static readonly ILog Log = LogManager.GetLogger(typeof(MainWindow));
        //private ObservableCollection<KnowledgeItem> _kiList;

        #endregion

        #region Methods

        /// <summary>
        /// Add KnowledgeItems Data in a XML file.
        /// </summary>
        /// <param name="ki">Specify the Knowledge Item Object</param>
        /// <returns>Boolean</returns>
        public int Add(KnowledgeItem ki)
        {
          
            if (ki == null) return -1;

            #region SQLCE
            try
            {
                var kiDataAdapter = new tblKnowledgeItemsTableAdapter();
                if (kiDataAdapter.
                        Insert1(ki.ClassLevel,
                                ki.Subject,
                                ki.Topic,
                                ki.TopicNo,
                                ki.Content,
                                ki.KIType) > 0)
                {
                    return Int32.Parse(kiDataAdapter.NewestID().ToString());
                }
                return -1;
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][Add]Error:" + e.Message);
                return -1;
            }

            #endregion
        }

        /// <summary>
        /// Update KnowledgeItems Data in a XML file.
        /// </summary>
        /// <param name="ki">Specify the Knowledge Item Object</param>
        /// <returns>Boolean</returns>
        public bool Update(KnowledgeItem ki)
        {

            if (ki == null) return false;
            try
            {
                return
                    (new tblKnowledgeItemsTableAdapter()).
                        Update1(ki.ClassLevel,
                                ki.Subject,
                                ki.Topic,
                                ki.TopicNo,
                                ki.Content,
                                ki.KIType,
                                ki.ID) > 0;
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][Update]Error:" + e.Message);
                return false;
            }
        }

        /// <summary>
        /// Delete KnowledgeItems Data in a XML file.
        /// </summary>
        /// <param name="knowledgeItem">Specify the Knowledge Item ID</param>
        /// <returns>Boolean</returns>
        public bool Delete(KnowledgeItem knowledgeItem)
        {

            if (knowledgeItem == null)
                return false;

            #region SQLCE
            try
            {
                return (new tblKnowledgeItemsTableAdapter()).Delete(knowledgeItem.ID) > 0;
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][Delete]Error:" + e.Message);
                return false;
            }

            #endregion
        }

        /// <summary>
        /// Contains KnowledgeItems Data in a XML file.
        /// </summary>
        /// <param name="knowledgeItem">Specify the Knowledge Item Object</param>
        /// <returns>Boolean</returns>
        public bool ContainsItem(KnowledgeItem knowledgeItem)
        {
            var flag = false;

            #region SQLDB
            if (knowledgeItem == null) return false;
            try
            {
                var connection = new SqlConnection { ConnectionString = SQLHelper.GetConnectionString() };
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = SQLHelper.SP_ContainsKI;
                    command.Parameters.Add("@ID", SqlDbType.Int).Value = knowledgeItem.ID;
                    var result = command.ExecuteReader();
                    if (result.HasRows)
                    {
                        flag = true;
                    }
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                Log.Error(ex.Message);
            }

            #endregion

            return flag;
        }

        public int GetMaxTopicNo()
        {

            #region SQLCE
            try
            {
                return (new tblTopicsTableAdapter()).GetMaxTopicNo() ?? 1;
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][GetMaxTopic]Error:" + e.Message);
                return 1;
            }

            #endregion

        }

        public ObservableCollection<KnowledgeItem> AdvanceSearch(KISearchCondition searchCondition)
        {
            var kiList = new ObservableCollection<KnowledgeItem>();

            #region SQLCE
            try
            {
                var knowledgeItemSet = new TLH_DB_DataSet.tblKnowledgeItemsDataTable();
                var knowledgeItemAdapter = new tblKnowledgeItemsTableAdapter();

                var rtfKeyword = "%" + (searchCondition.Keyword ?? "").ToLower().ConvertToRtf() + "%";

                knowledgeItemSet = knowledgeItemAdapter.
                                                    AdvanceSearch(
                                                        searchCondition.SelectedClassLevel,
                                                        searchCondition.Subject,
                                                        searchCondition.MinTopicNo,
                                                        searchCondition.MaxTopicNo,
                                                        rtfKeyword
                                                    );
                //// filter with topic
                //if (searchCondition.MaxTopicNo >= 1)
                //{
                //    if (searchCondition.SelectedClassLevel >= 1)
                //    {
                //        // filter with topic + class + subject
                //        if (!string.IsNullOrEmpty(searchCondition.Subject))
                //        {
                //            knowledgeItemSet = knowledgeItemAdapter.Search()
                //        }
                //        else
                //        {
                //            // and filter with topic + class
                //            knowledgeItemSet = knowledgeItemAdapter.SearchByTopicAndClass(
                //                searchCondition.SelectedClassLevel,
                //                searchCondition.MinTopicNo,
                //                searchCondition.MaxTopicNo);
                //        }
                //    }
                //    // and filter with topic + subject
                //    else if (!string.IsNullOrEmpty(searchCondition.Subject))
                //    {
                //        knowledgeItemSet = knowledgeItemAdapter.SearchByTopicAndSubject(
                //                                    searchCondition.Subject,
                //                                    searchCondition.MinTopicNo,
                //                                    searchCondition.MaxTopicNo);
                //    }
                //    // only filter by topic no
                //    else
                //    {
                //        knowledgeItemSet = knowledgeItemAdapter.SearchByTopicNo(
                //                                    searchCondition.MinTopicNo,
                //                                    searchCondition.MaxTopicNo);
                //    }
                //}
                //// no topic filter
                //else if (searchCondition.SelectedClassLevel >= 1)
                //{
                //    // filter class + subject
                //    if (!string.IsNullOrEmpty(searchCondition.Subject))
                //    {
                //        knowledgeItemSet = knowledgeItemAdapter.SearchByClassAndSubject(
                //            searchCondition.SelectedClassLevel,
                //            searchCondition.Subject);
                //    }
                //    // only filter with class
                //    else
                //    {
                //        knowledgeItemSet = knowledgeItemAdapter.SearchByClass(searchCondition.SelectedClassLevel);
                //    }
                //}
                //else if (!string.IsNullOrEmpty(searchCondition.Subject))
                //{
                //    // filter with subject only
                //    knowledgeItemSet = knowledgeItemAdapter.SearchBySubject(searchCondition.Subject);
                //}

                //if (!string.IsNullOrEmpty(searchCondition.Keyword))
                //{
                foreach (var result in knowledgeItemSet)
                {
                    kiList.Add(new KnowledgeItem
                                   {
                                       ClassLevel = result.Class,
                                       Subject = result.Subject,
                                       Topic = result.Topic,
                                       TopicNo = result.TopicNo,
                                       KIType = result.Type,
                                       Content = result.Content
                                   });
                }
                //}
                //else
                //{
                //    foreach (var result in knowledgeItemSet)
                //    {
                //        kiList.Add(new KnowledgeItem
                //        {
                //            ID = result.ID,
                //            ClassLevel = result.Class,
                //            Subject = result.Subject,
                //            Topic = result.Topic,
                //            TopicNo = result.TopicNo,
                //            KIType = result.Type,
                //            Content = result.Content
                //        });
                //    }
                //}
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][AdvanceSearch]Error:" + e.Message);
            }


            #endregion)

            return kiList;
        }

        /// <summary>
        /// Search KnowledgeItems Data in a XML file.
        /// </summary>
        /// <param name="searchCondition">Specify the search condition</param>
        /// <returns>ObservableCollection of KnowledgeItem</returns>
        public ObservableCollection<KnowledgeItem> Search(KISearchCondition searchCondition)
        {
            var kiList = new ObservableCollection<KnowledgeItem>();

            #region SQLCE
            try
            {
                // if keyword is empty, load all
                if (string.IsNullOrEmpty(searchCondition.Keyword)) return GetAll();

                // use normal string to search subject and other _activity
                // use rtf to search content

                var rtfKeyword = "%" + searchCondition.Keyword.ToLower().ConvertToRtf() + "%";
                var knowledgeItemSet =
                    (new tblKnowledgeItemsTableAdapter()).
                                            Search(
                                                        "%" + searchCondition.Keyword + "%"
                                                        , rtfKeyword);
                // add dataset to result list
                foreach (var result in knowledgeItemSet)
                {
                    kiList.Add(new KnowledgeItem
                                   {
                                       ID = result.ID,
                                       ClassLevel = result.Class,
                                       Subject = result.Subject,
                                       Topic = result.Topic,
                                       TopicNo = result.TopicNo,
                                       KIType = result.Type,
                                       Content = result.Content
                                   });
                }
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][Search]Error:" + e.Message);
            }

            #endregion)

            return kiList;
        }

        /// <summary>
        /// Get all KIs
        /// </summary>
        /// <returns></returns>
        public ObservableCollection<KnowledgeItem> GetAll()
        {
            var kiList = new ObservableCollection<KnowledgeItem>();

            #region SQLCE
            try
            {
                var knowledgeItemSet = new TLH_DB_DataSet.tblKnowledgeItemsDataTable();
                (new tblKnowledgeItemsTableAdapter()).Fill(knowledgeItemSet);
                foreach (var data in knowledgeItemSet)
                {
                    kiList.Add(new KnowledgeItem
                                   {
                                       ID = data.ID,
                                       ClassLevel = data.Class,
                                       Subject = data.Subject,
                                       Topic = data.Topic,
                                       TopicNo = data.TopicNo,
                                       Content = data.Content,
                                       KIType = data.Type
                                   });
                }
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][GetAll]Error:" + e.Message);
            }


            #endregion

            return kiList;

        }

        /// <summary>
        /// Get all subject in a XML file.
        /// </summary>
        /// <returns>ObservableCollection of string</returns>
        public ObservableCollection<string> GetSubjectList()
        {
            var subjectObservableCollection = new ObservableCollection<string>();

            #region SQLCE
            try
            {
                var subjectDataSet = (new tblSubjectsTableAdapter()).GetData();
                foreach (var subject in subjectDataSet)
                {
                    if (!subjectObservableCollection.Contains(subject.Name))
                        subjectObservableCollection.Add(subject.Name);
                }

            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][GetSubjectObservableCollection]Error:" + e.Message);
            }

            #endregion

            return subjectObservableCollection;
        }

        /// <summary>
        /// Get all topic in a XML file.
        /// </summary>
        /// <returns>ObservableCollection of string</returns>
        public ObservableCollection<string> GetTopicList()
        {
            var topicObservableCollection = new ObservableCollection<string>();

            #region SQLCE
            try
            {

                var topicDataSet = (new tblTopicsTableAdapter()).GetTopicList();
                foreach (var topic in topicDataSet)
                {
                    topicObservableCollection.Add(topic.Name);
                }
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][GetTopicObservableCollection]Error:" + e.Message);
            }

            #endregion

            return topicObservableCollection;
        }

        /// <summary>
        /// Get subject in class in a XML file.
        /// </summary>
        /// <param name="classLevel">Specify the class level</param>
        /// <returns>ObservableCollection of string</returns>
        public ObservableCollection<string> GetSubjectByClass(int classLevel)
        {
            var subjectObservableCollection = new ObservableCollection<string>();

            #region SQLCE

            try
            {
                var subjectDataSet = (new tblSubjectsTableAdapter()).GetSubjectByClass(classLevel);
                foreach (var subject in subjectDataSet)
                {
                    subjectObservableCollection.Add(subject.Name);
                }
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][GetSubjectByClass]Error:" + e.Message);
            }

            #endregion)

            return subjectObservableCollection;
        }

        /// <summary>
        /// Get topic of subject in class in a XML file.
        /// </summary>
        /// <param name="classLevel">Specify the class level</param>
        /// <param name="subject">Specify the subject</param>
        /// <returns>ObservableCollection of string</returns>
        public ObservableCollection<string> GetTopicByClassAndSubject(int classLevel, string subject)
        {
            var topicObservableCollection = new ObservableCollection<string>();

            #region SQLCE

            try
            {
                if (string.IsNullOrEmpty(subject) || classLevel < 1) return GetTopicList();
                var topicDataSet = (new tblTopicsTableAdapter()).GetTopicByClassAndSubject(subject,classLevel);
                foreach (var topic in topicDataSet)
                {
                    topicObservableCollection.Add(topic.Name);
                }
            }
            catch (Exception e)
            {
                Log.Debug("[KIAccessor][GetTopicByClassAndSubject]Error:" + e.Message);
            }

            #endregion

            return topicObservableCollection;
        }

        #endregion

    }
}
